package com.maizhi.util;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;



public class xlsxUtil {
	
	Logger logger = Logger.getLogger(xlsxUtil.class);
		
	public  List<List<String>> parseXlsByPoi(File file) {
        HSSFWorkbook wb = null;
        try {
            wb = new HSSFWorkbook(new FileInputStream(file));
        } catch (Exception e) {
            e.printStackTrace();
        }
        List<List<String>> items = new ArrayList<List<String>>();
        if (wb != null && wb.getNumberOfSheets() > 0) {
            try {
                // get first sheet object
                HSSFSheet sheet = wb.getSheetAt(0);
                // get current Workbook rows
                int rowNum = sheet.getLastRowNum();
                List<String> item = null;
                for (int i = 0;i < rowNum;i++) {
                    // get current row cells
                    HSSFRow row = sheet.getRow(i);
                    int cellNum = row.getLastCellNum();
                    if (cellNum > 0) {
                        item = new ArrayList<String>();
                        for (int j = 0;j < cellNum;j++) {
                            HSSFCell cell = row.getCell(j);
                            if (null != cell) {
                                switch (cell.getCellType()) {
                                case HSSFCell.CELL_TYPE_NUMERIC: // 数字
                                    item.add(Double.toString(cell.getNumericCellValue()));
                                    break;
                                case HSSFCell.CELL_TYPE_STRING: // 字符串
                                    item.add(cell.getStringCellValue());
                                    break;
                                case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                                    item.add(Boolean.toString(cell.getBooleanCellValue()));
                                    break;
                                case HSSFCell.CELL_TYPE_FORMULA: // 公式
                                    item.add(cell.getCellFormula());
                                    break;
                                case HSSFCell.CELL_TYPE_BLANK: // 空值
                                    item.add("");
                                    break;
                                case HSSFCell.CELL_TYPE_ERROR: // 故障
                                    item.add("");
                                    break;
                                default:
                                    item.add("");
                                    break;
                                }
                            } else {
                                item.add("");
                            }
                        }
                        items.add(item);
                    } else {
                        throw new RuntimeException("No cells found in excel .");
                    }
                }
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        } else {
            throw new RuntimeException("Please select a valided excel file !");
        }
        return items;
    }
	
	
	public  List<List<String>> parseXlsxByPoi(File file) {
        XSSFWorkbook wb = null;
        try {
            wb = new XSSFWorkbook(new FileInputStream(file));
        } catch (Exception e) {
            e.printStackTrace();
        }
        List<List<String>> items = new ArrayList<List<String>>();
        if (wb != null && wb.getNumberOfSheets() > 0) {
            try {
                // get first sheet object
                XSSFSheet sheet = wb.getSheetAt(0);
                // get current Workbook rows
                int rowNum = sheet.getLastRowNum();
                List<String> item = null;
                for (int i = 0;i < rowNum;i++) {
                    // get current row cells
                    XSSFRow row = sheet.getRow(i);
                    int cellNum = row.getLastCellNum();
                    if (cellNum > 0) {
                        item = new ArrayList<String>();
                        for (int j = 0;j < cellNum;j++) {
                            XSSFCell cell = row.getCell(j);
                            if (null != cell) {
                                switch (cell.getCellType()) {
                                case XSSFCell.CELL_TYPE_NUMERIC: // 数字
                                    item.add(Double.toString(cell.getNumericCellValue()));
                                    break;
                                case XSSFCell.CELL_TYPE_STRING: // 字符串
                                    item.add(cell.getStringCellValue());
                                    break;
                                case XSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                                    item.add(Boolean.toString(cell.getBooleanCellValue()));
                                    break;
                                case XSSFCell.CELL_TYPE_FORMULA: // 公式
                                    item.add(cell.getCellFormula());
                                    break;
                                case XSSFCell.CELL_TYPE_BLANK: // 空值
                                    item.add("");
                                    break;
                                case XSSFCell.CELL_TYPE_ERROR: // 故障
                                    item.add("");
                                    break;
                                default:
                                    item.add("");
                                    break;
                                }
                            } else {
                                item.add("");
                            }
                        }
                        items.add(item);
                    } else {
                        throw new RuntimeException("No cells found in excel .");
                    }
                }
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        } else {
            throw new RuntimeException("Please select a valided excel file !");
        }
        return items;
    }
	
	
	public  List<List<String>> parseXlsXlsxByPoi(File file) {
        org.apache.poi.ss.usermodel.Workbook wb = null;
        try {
            wb = new XSSFWorkbook(new FileInputStream(file));
        } catch (Exception e) {
            try {
                wb = new HSSFWorkbook(new FileInputStream(file));
            } catch (Exception e2) {
            	e2.printStackTrace();
                throw new RuntimeException("Unknow File Type!!!");
            }
        }
        
       logger.debug("wb : "+wb);
        
        List<List<String>> items = new ArrayList<List<String>>();
        
        
        logger.debug("wb.getNumberOfSheets() : "+wb.getNumberOfSheets());
        
        if (wb != null && wb.getNumberOfSheets() > 0) {
            try {
                for(int k=0;k<wb.getNumberOfSheets();k++){
                	//get first sheet object
                    org.apache.poi.ss.usermodel.Sheet sheet = wb.getSheetAt(k);
                    logger.debug("sheet : "+sheet+"  sheet.getSheetName : "+sheet.getSheetName());
                    // get current Workbook rows
                    int rowNum = sheet.getLastRowNum();
                    logger.debug("rowNum : "+rowNum);
                    
                    if(rowNum>0){
                    	List<String> item = null;
                        for (int i = 0;i <=rowNum;i++) {
                            // get current row cells
                            Row row = sheet.getRow(i);
                            if (row == null) {
                                row = sheet.createRow(i);
                           }
                            int cellNum = row.getLastCellNum();
                            
                            //logger.debug("cellNum : "+cellNum);
                            if (cellNum > 0) {
                                item = new ArrayList<String>();
                                for (int j = 0;j < cellNum;j++) {
                                    org.apache.poi.ss.usermodel.Cell cell = row.getCell(j);
                                    if (null != cell) {
                                        switch (cell.getCellType()) {
                                        case XSSFCell.CELL_TYPE_NUMERIC: // 数字
                                            item.add(Double.toString(cell.getNumericCellValue()));
                                        	//item.add(StringUtil.nullTrim(cell.getNumericCellValue()));
                                            break;
                                        case XSSFCell.CELL_TYPE_STRING: // 字符串
                                            item.add(cell.getStringCellValue());
                                            break;
                                        case XSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                                            item.add(Boolean.toString(cell.getBooleanCellValue()));
                                            break;
                                        case XSSFCell.CELL_TYPE_FORMULA: // 公式
                                            item.add(cell.getCellFormula());
                                            break;
                                        case XSSFCell.CELL_TYPE_BLANK: // 空值
                                            item.add("");
                                            break;
                                        case XSSFCell.CELL_TYPE_ERROR: // 故障
                                            item.add("");
                                            break;
                                        default:
                                            item.add("");
                                            break;
                                        }
                                    } else {
                                        item.add("");
                                    }
                                    //logger.debug("item : "+item);
                                }
                                items.add(item);
                                logger.debug(" items.size() : "+items.size());
                            }  
                        }
                    }
                }
            } catch (Exception e) {
            	e.printStackTrace();
                throw new RuntimeException(e);
            }
        } else {
            throw new RuntimeException("Please select a valided excel file !");
        }
        return items;
    }
	
	
	public  Map<String,List<String>> parseXlsXlsxByPoi2(File file) {
        org.apache.poi.ss.usermodel.Workbook wb = null;
        try {
            wb = new XSSFWorkbook(new FileInputStream(file));
        } catch (Exception e) {
            try {
                wb = new HSSFWorkbook(new FileInputStream(file));
            } catch (Exception e2) {
            	e2.printStackTrace();
                throw new RuntimeException("Unknow File Type!!!");
            }
        }
        
       logger.debug("wb : "+wb);
        
        Map<String,List<String>> items = new HashMap();
        int m=1;
        
        logger.debug("wb.getNumberOfSheets() : "+wb.getNumberOfSheets());
        
        if (wb != null && wb.getNumberOfSheets() > 0) {
            try {
                for(int k=0;k<wb.getNumberOfSheets();k++){
                	//get first sheet object
                    org.apache.poi.ss.usermodel.Sheet sheet = wb.getSheetAt(k);
                    logger.debug("sheet : "+sheet+"  sheet.getSheetName : "+sheet.getSheetName());
                    // get current Workbook rows
                    int rowNum = sheet.getLastRowNum();
                    logger.debug("rowNum : "+rowNum);
                    
                    if(rowNum>0){
                    	List<String> item = null;
                        for (int i = 0;i <=rowNum;i++) {
                            // get current row cells
                            Row row = sheet.getRow(i);
                            if (row == null) {
                                row = sheet.createRow(i);
                           }
                            int cellNum = row.getLastCellNum();
                            
                            //logger.debug("cellNum : "+cellNum);
                            if (cellNum > 0) {
                                item = new ArrayList<String>();
                                for (int j = 0;j < cellNum;j++) {
                                    org.apache.poi.ss.usermodel.Cell cell = row.getCell(j);
                                    if (null != cell) {
                                        switch (cell.getCellType()) {
                                        case XSSFCell.CELL_TYPE_NUMERIC: // 数字
                                            item.add(Double.toString(cell.getNumericCellValue()));
                                        	//item.add(StringUtil.nullTrim(cell.getNumericCellValue()));
                                            break;
                                        case XSSFCell.CELL_TYPE_STRING: // 字符串
                                            item.add(cell.getStringCellValue());
                                            break;
                                        case XSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                                            item.add(Boolean.toString(cell.getBooleanCellValue()));
                                            break;
                                        case XSSFCell.CELL_TYPE_FORMULA: // 公式
                                            item.add(cell.getCellFormula());
                                            break;
                                        case XSSFCell.CELL_TYPE_BLANK: // 空值
                                            item.add("");
                                            break;
                                        case XSSFCell.CELL_TYPE_ERROR: // 故障
                                            item.add("");
                                            break;
                                        default:
                                            item.add("");
                                            break;
                                        }
                                    } else {
                                        item.add("");
                                    }
                                    //logger.debug("item : "+item);
                                }
                                items.put((k+1)+":"+sheet.getSheetName()+":"+m, item);
                                m++;
                                logger.debug(" items.size() : "+items.size());
                            }  
                        }
                    }
                }
            } catch (Exception e) {
            	e.printStackTrace();
                throw new RuntimeException(e);
            }
        } else {
            throw new RuntimeException("Please select a valided excel file !");
        }
        return items;
    }
}

